<?php

class ReportController extends BaseController {

    public function getOrder() {
        Session::forget('list');
        if (Session::get('bulan') == "") {
            $b = getdate();
            $b = $b[0];
            $b = date('m', $b);
            Session::put('bulan', $b);
        } else {
            $b = Session::get('bulan');
        }
        if (Session::get('tahun') == "") {
            $t = getdate();
            $t = $t[0];
            $t = date('Y', $t);
            Session::put('tahun', $t);
        } else {
            $t = Session::get('tahun');
        }

        $totalorder = DB::table('sys_order')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderbrosur = DB::table('sys_order')
                ->where('order_name', 'brochure')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderidcard = DB::table('sys_order')
                ->where('order_name', 'idcard')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordernota = DB::table('sys_order')
                ->where('order_name', 'nota')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpackaging = DB::table('sys_order')
                ->where('order_name', 'packaging')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordertshirt = DB::table('sys_order')
                ->where('order_name', 'tshirt')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        $orderlistorder = DB::table('sys_order')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_date', 'DESC')
                ->paginate(10);
        return View::make('admin.report.order', compact('totalorder', $totalorder, 'totalorderbrosur', $totalorderbrosur, 'totalorderidcard', $totalorderidcard, 'totalordernota', $totalordernota, 'totalorderpackaging', $totalorderpackaging, 'totalordertshirt', $totalordertshirt, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

    public function postOrder() {
        $b = Session::get('bulan');
        $t = Session::get('tahun');

        if (Input::get('filter') == 'type') {
            Session::put('list', Input::get('typeby'));
            if (Input::get('typeby') == 'all') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            } else {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('order_name', Input::get('typeby'))
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            }
        } else if (Input::get('filter') == 'keyword') {
            if (Input::get('filterby') == 'code') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('order_code', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            } else if (Input::get('filterby') == 'name') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('customer_name', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            }
        } else {
            if (Session::get('list') == 'all') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            } else {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('order_name', Session::get('list'))
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            }
        }

        $totalorder = DB::table('sys_order')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderbrosur = DB::table('sys_order')
                ->where('order_name', 'brochure')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderidcard = DB::table('sys_order')
                ->where('order_name', 'idcard')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordernota = DB::table('sys_order')
                ->where('order_name', 'nota')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpackaging = DB::table('sys_order')
                ->where('order_name', 'packaging')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordertshirt = DB::table('sys_order')
                ->where('order_name', 'tshirt')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();



        return View::make('admin.report.order', compact('totalorder', $totalorder, 'totalorderbrosur', $totalorderbrosur, 'totalorderidcard', $totalorderidcard, 'totalordernota', $totalordernota, 'totalorderpackaging', $totalorderpackaging, 'totalordertshirt', $totalordertshirt, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

    public function postOrderbulan() {
        Session::forget('list');
        if (Input::get('bulan') == "") {
            $b = Session::get('bulan');
        } else {
            $b = Input::get('bulan');
            Session::put('bulan', Input::get('bulan'));
        }
        if (Input::get('tahun') == "") {
            $t = Session::get('tahun');
        } else {
            $t = Input::get('tahun');
            Session::put('tahun', Input::get('tahun'));
        }


        $orderlistorder = DB::table('sys_order')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_date', 'DESC')
                ->paginate(10);
        $totalorder = DB::table('sys_order')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderbrosur = DB::table('sys_order')
                ->where('order_name', 'brochure')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderidcard = DB::table('sys_order')
                ->where('order_name', 'idcard')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordernota = DB::table('sys_order')
                ->where('order_name', 'nota')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpackaging = DB::table('sys_order')
                ->where('order_name', 'packaging')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordertshirt = DB::table('sys_order')
                ->where('order_name', 'tshirt')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        return View::make('admin.report.order', compact('totalorder', $totalorder, 'totalorderbrosur', $totalorderbrosur, 'totalorderidcard', $totalorderidcard, 'totalordernota', $totalordernota, 'totalorderpackaging', $totalorderpackaging, 'totalordertshirt', $totalordertshirt, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

    public function getStatus() {
        Session::forget('status');
        if (Session::get('bulan') == "") {
            $b = getdate();
            $b = $b[0];
            $b = date('m', $b);
            Session::put('bulan', $b);
        } else {
            $b = Session::get('bulan');
        }
        if (Session::get('tahun') == "") {
            $t = getdate();
            $t = $t[0];
            $t = date('Y', $t);
            Session::put('tahun', $t);
        } else {
            $t = Session::get('tahun');
        }

        $totalorder = DB::table('sys_order')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpending = DB::table('sys_order')
                ->where('order_status', 'Pending')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderapprove = DB::table('sys_order')
                ->where('order_status', 'Approve')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderonprocess = DB::table('sys_order')
                ->where('order_status', 'On Process')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderfinished = DB::table('sys_order')
                ->where('order_status', 'Finished')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderdelivered = DB::table('sys_order')
                ->where('order_status', 'Delivered')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordercancel = DB::table('sys_order')
                ->where('order_status', 'Cancel')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        $orderlistorder = DB::table('sys_order')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_date', 'DESC')
                ->paginate(10);
        return View::make('admin.report.status', compact('totalorder', $totalorder, 'totalorderpending', $totalorderpending, 'totalorderapprove', $totalorderapprove, 'totalorderonprocess', $totalorderonprocess, 'totalorderfinished', $totalorderfinished, 'totalorderdelivered', $totalorderdelivered, 'totalordercancel', $totalordercancel, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

    public function postStatus() {
        $b = Session::get('bulan');
        $t = Session::get('tahun');

        if (Input::get('filter') == 'status') {
            Session::put('status', Input::get('status'));
            if (Input::get('status') == 'all') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            } else {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('order_status', Input::get('status'))
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            }
        } else if (Input::get('filter') == 'keyword') {
            if (Input::get('filterby') == 'code') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('order_code', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            } else if (Input::get('filterby') == 'name') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('customer_name', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            }
        } else {
            if (Session::get('status') == 'all') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            } else {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                        ->where('order_status', Session::get('status'))
                        ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_date', 'DESC')
                        ->paginate(10);
            }
        }

        $totalorder = DB::table('sys_order')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpending = DB::table('sys_order')
                ->where('order_status', 'Pending')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderapprove = DB::table('sys_order')
                ->where('order_status', 'Approve')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderonprocess = DB::table('sys_order')
                ->where('order_status', 'On Process')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderfinished = DB::table('sys_order')
                ->where('order_status', 'Finished')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderdelivered = DB::table('sys_order')
                ->where('order_status', 'Delivered')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordercancel = DB::table('sys_order')
                ->where('order_status', 'Cancel')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();



        return View::make('admin.report.status', compact('totalorder', $totalorder, 'totalorderpending', $totalorderpending, 'totalorderapprove', $totalorderapprove, 'totalorderonprocess', $totalorderonprocess, 'totalorderfinished', $totalorderfinished, 'totalorderdelivered', $totalorderdelivered, 'totalordercancel', $totalordercancel, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

    public function postStatusbulan() {
        Session::forget('status');
        if (Input::get('bulan') == "") {
            $b = Session::get('bulan');
        } else {
            $b = Input::get('bulan');
            Session::put('bulan', Input::get('bulan'));
        }
        if (Input::get('tahun') == "") {
            $t = Session::get('tahun');
        } else {
            $t = Input::get('tahun');
            Session::put('tahun', Input::get('tahun'));
        }

        $orderlistorder = DB::table('sys_order')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_date', 'DESC')
                ->paginate(10);
        $totalorder = DB::table('sys_order')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpending = DB::table('sys_order')
                ->where('order_status', 'Pending')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderapprove = DB::table('sys_order')
                ->where('order_status', 'Approve')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderonprocess = DB::table('sys_order')
                ->where('order_status', 'On Process')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderfinished = DB::table('sys_order')
                ->where('order_status', 'Finished')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderdelivered = DB::table('sys_order')
                ->where('order_status', 'Delivered')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordercancel = DB::table('sys_order')
                ->where('order_status', 'Cancel')
                ->whereBetween('order_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        Session::put('bulan', Input::get('bulan'));
        Session::put('tahun', Input::get('tahun'));
        return View::make('admin.report.status', compact('totalorder', $totalorder, 'totalorderpending', $totalorderpending, 'totalorderapprove', $totalorderapprove, 'totalorderonprocess', $totalorderonprocess, 'totalorderfinished', $totalorderfinished, 'totalorderdelivered', $totalorderdelivered, 'totalordercancel', $totalordercancel, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }


    public function getBonus() {
        $b = getdate();
        $b = $b[0];
        $b = date('m', $b);

        $t = getdate();
        $t = $t[0];
        $t = date('Y', $t);

        Session::put('bulan', $b);
        Session::put('tahun', $t);

        $regionalall = DB::table('sys_member')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('member_regional_id')
                ->get();

        $jumlahagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        $jumlahbonusref = DB::table('sys_bonus_log')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->sum('bonus_log_referal');

        $jumlahbonusorder = DB::table('sys_bonus_log')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->sum('bonus_log_order');


        $jumlahagenaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Aktif')
                ->count();

        $jumlahagentidakaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Tidak aktif')
                ->count();

        $logbonus = DB::table('sys_bonus_log')->get();

        $dataagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->join('sys_bonus_log', 'bonus_log_agen_id', '=', 'member_agen_id')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->orderBy('member_agen_id', 'asc')
//                ->groupBy('member_agen_id')
                ->paginate(10);

        $datalogagen = DB::table('sys_bonus_log')
                ->join('sys_member', 'member_agen_id', '=', 'bonus_log_agen_id')
                ->join('sys_agen', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                //->where('bonus_log_agen_id')
                ->paginate(10);
        return View::make('admin.report.bonus', compact('t', $t, 'b', $b,$datalogagen,'datalogagen', $logbonus, 'logbonus', 'jumlahbonusref', $jumlahbonusref, 'jumlahbonusorder', $jumlahbonusorder, 'jumlahagen', $jumlahagen, 'jumlahagentidakaktif', $jumlahagentidakaktif, 'jumlahagenaktif', $jumlahagenaktif, 'dataagen', $dataagen));

        //return View::make('admin.report.bonus');
    }

    public function postBonus() {
        $b = Session::get('bulan');
        $t = Session::get('tahun');

        $regionalall = DB::table('sys_member')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('member_regional_id')
                ->get();

        $jumlahagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        $jumlahbonusref = DB::table('sys_bonus_log')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->sum('bonus_log_referal');

        $jumlahbonusorder = DB::table('sys_bonus_log')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->sum('bonus_log_order');


        $jumlahagenaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Aktif')
                ->count();

        $jumlahagentidakaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Tidak aktif')
                ->count();

        $logbonus = DB::table('sys_bonus_log')->get();

                $datalogagen = DB::table('sys_bonus_log')
                ->join('sys_member', 'member_agen_id', '=', 'bonus_log_agen_id')
                ->join('sys_agen', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->paginate(10);
                
            if (Input::get('filterby') == 'agencode') {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('sys_bonus_log', 'bonus_log_agen_id', '=', 'member_agen_id')
                        ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->where('agen_code', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->orderBy('member_agen_id', 'asc')
                        ->paginate(10);
            } else if (Input::get('filterby') == 'agenname') {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('sys_bonus_log', 'bonus_log_agen_id', '=', 'member_agen_id')
                        ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->where('member_name', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->orderBy('member_agen_id', 'asc')
                        ->paginate(10);
            } else if (Input::get('filterby') == 'referalcode') {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('sys_bonus_log', 'bonus_log_agen_id', '=', 'member_agen_id')
                        ->leftjoin('sys_agen AS ch', 'ch.agen_id', '=', 'sys_agen.agen_sponsor_id')
                        ->where('ch.agen_code', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->select('bonus_log_date','bonus_log_referal','bonus_log_order','bonus_log_agen_id','sys_agen.agen_code', 'sys_agen.agen_sponsor_id', 'sys_agen.agen_id', 'member_name', 'member_status', 'member_email', 'member_phone', 'member_address', 'member_joindate', 'member_regional_id', 'member_provinsi_id')
                        ->paginate(10);
            }else {
            $dataagen = DB::table('sys_agen')
                    ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                    ->join('sys_bonus_log', 'bonus_log_agen_id', '=', 'member_agen_id')
                    ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                    ->orderBy('member_agen_id', 'asc')
                    ->paginate(10);
           }


        return View::make('admin.report.bonus', compact('t', $t, 'b', $b, $logbonus, 'logbonus',$datalogagen,'datalogagen', 'jumlahbonusref', $jumlahbonusref, 'jumlahbonusorder', $jumlahbonusorder, 'jumlahagen', $jumlahagen, 'jumlahagentidakaktif', $jumlahagentidakaktif, 'jumlahagenaktif', $jumlahagenaktif, 'dataagen', $dataagen));
    }
    
    public function postBonusbulan() {
        Session::forget('typeby');
        if (Input::get('bulan') == "") {
            $b = Session::get('bulan');
        } else {
            $b = Input::get('bulan');
            Session::put('bulan', Input::get('bulan'));
        }
        if (Input::get('tahun') == "") {
            $t = Session::get('tahun');
        } else {
            $t = Input::get('tahun');
            Session::put('tahun', Input::get('tahun'));
        }

        $regionalall = DB::table('sys_member')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('member_regional_id')
                ->get();

        $jumlahagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        $jumlahbonusref = DB::table('sys_bonus_log')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->sum('bonus_log_referal');

        $jumlahbonusorder = DB::table('sys_bonus_log')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->sum('bonus_log_order');


        $jumlahagenaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Aktif')
                ->count();

        $jumlahagentidakaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Tidak aktif')
                ->count();

        $logbonus = DB::table('sys_bonus_log')->get();

        $dataagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->join('sys_bonus_log', 'bonus_log_agen_id', '=', 'member_agen_id')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->orderBy('member_agen_id', 'asc')
                ->paginate(10);

        $datalogagen = DB::table('sys_bonus_log')
                ->join('sys_member', 'member_agen_id', '=', 'bonus_log_agen_id')
                ->join('sys_agen', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('bonus_log_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->paginate(10);
        return View::make('admin.report.bonus', compact('t', $t, 'b', $b,$datalogagen,'datalogagen', $logbonus, 'logbonus', 'jumlahbonusref', $jumlahbonusref, 'jumlahbonusorder', $jumlahbonusorder, 'jumlahagen', $jumlahagen, 'jumlahagentidakaktif', $jumlahagentidakaktif, 'jumlahagenaktif', $jumlahagenaktif, 'dataagen', $dataagen));

    }
   
  

    public function getAgen() {
        Session::forget('typeby');
        if (Session::get('bulan') == "") {
            $b = getdate();
            $b = $b[0];
            $b = date('m', $b);
            Session::put('bulan', $b);
        } else {
            $b = Session::get('bulan');
        }
        if (Session::get('tahun') == "") {
            $t = getdate();
            $t = $t[0];
            $t = date('Y', $t);
            Session::put('tahun', $t);
        } else {
            $t = Session::get('tahun');
        }

        $regionalall = DB::table('sys_member')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('member_regional_id')
                ->get();

        $jumlahagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();


        $jumlahagenaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Aktif')
                ->count();

        $jumlahagentidakaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Tidak aktif')
                ->count();
        $dataagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->paginate(10);
        $provinsi = provinsi::all();
        $regional = regional::all();
        return View::make('admin.report.agen', compact('t', $t, 'b', $b, 'regionalall', $regionalall, 'jumlahagen', $jumlahagen, 'jumlahagentidakaktif', $jumlahagentidakaktif, 'jumlahagenaktif', $jumlahagenaktif, 'provinsi', $provinsi, 'regional', $regional, 'dataagen', $dataagen));
    }

    public function postAgen() {
        $b = Session::get('bulan');
        $t = Session::get('tahun');

        $regionalall = DB::table('sys_member')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('member_regional_id')
                ->get();

        $jumlahagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();


        $jumlahagenaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Aktif')
                ->count();

        $jumlahagentidakaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Tidak aktif')
                ->count();


        if (Input::get('filter') == 'type') {
            Session::put('typeby', Input::get('typeby'));
            if (Input::get('typeby') == 'all') {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                        ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                        ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->paginate(10);
            } else {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                        ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                        ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->where('member_status', '=', Input::get('typeby'))
                        ->paginate(10);
            }
        } else if (Input::get('filter') == 'keyword') {
            if (Input::get('filterby') == 'agencode') {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                        ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                        ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->where('agen_code', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->paginate(10);
            } else if (Input::get('filterby') == 'agenname') {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                        ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                        ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->where('member_name', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->paginate(10);
            } else if (Input::get('filterby') == 'referalcode') {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                        ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                        ->leftjoin('sys_agen AS ch', 'ch.agen_id', '=', 'sys_agen.agen_sponsor_id')
                        ->where('ch.agen_code', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('sys_agen.agen_id')
                        ->select('sys_agen.agen_code', 'sys_agen.agen_sponsor_id', 'sys_agen.agen_id', 'member_name', 'member_status', 'member_email', 'member_phone', 'member_address', 'member_joindate', 'member_regional_id', 'member_provinsi_id', 'regional_name', 'provinsi_name')
                        ->paginate(10);
            }
        } else {
            if (Session::get('typeby') == 'all') {
                $orderlistorder = DB::table('sys_order')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                        ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                        ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->paginate(10);
            } else {
                $dataagen = DB::table('sys_agen')
                        ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                        ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                        ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                        ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->where('member_status', '=', Session::get('typeby'))
                        ->paginate(10);
            }
        }

        $provinsi = provinsi::all();
        $regional = regional::all();
        return View::make('admin.report.agen', compact('t', $t, 'b', $b, 'regionalall', $regionalall, 'jumlahagen', $jumlahagen, 'jumlahagentidakaktif', $jumlahagentidakaktif, 'jumlahagenaktif', $jumlahagenaktif, 'provinsi', $provinsi, 'regional', $regional, 'dataagen', $dataagen));
    }

    public function postAgenbulan() {
        Session::forget('typeby');
        if (Input::get('bulan') == "") {
            $b = Session::get('bulan');
        } else {
            $b = Input::get('bulan');
            Session::put('bulan', Input::get('bulan'));
        }
        if (Input::get('tahun') == "") {
            $t = Session::get('tahun');
        } else {
            $t = Input::get('tahun');
            Session::put('tahun', Input::get('tahun'));
        }

        $regionalall = DB::table('sys_member')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('member_regional_id')
                ->get();

        $jumlahagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();


        $jumlahagenaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Aktif')
                ->count();

        $jumlahagentidakaktif = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('member_status', 'Tidak aktif')
                ->count();
        $dataagen = DB::table('sys_agen')
                ->join('sys_member', 'member_agen_id', '=', 'agen_id')
                ->join('ref_provinsi', 'member_provinsi_id', '=', 'provinsi_id')
                ->join('ref_regional', 'member_regional_id', '=', 'regional_id')
                ->whereBetween('member_joindate', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->paginate(10);
        $provinsi = provinsi::all();
        $regional = regional::all();
        return View::make('admin.report.agen', compact('t', $t, 'b', $b, 'regionalall', $regionalall, 'jumlahagen', $jumlahagen, 'jumlahagentidakaktif', $jumlahagentidakaktif, 'jumlahagenaktif', $jumlahagenaktif, 'provinsi', $provinsi, 'regional', $regional, 'dataagen', $dataagen));
    }




    public function getProduk() {
        Session::forget('list');
        if (Session::get('bulan') == "") {
            $b = getdate();
            $b = $b[0];
            $b = date('m', $b);
            Session::put('bulan', $b);
        } else {
            $b = Session::get('bulan');
        }
        if (Session::get('tahun') == "") {
            $t = getdate();
            $t = $t[0];
            $t = date('Y', $t);
            Session::put('tahun', $t);
        } else {
            $t = Session::get('tahun');
        }

        $totalorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderbrosur = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('order_name', 'brochure')
                ->count();
        $totalorderidcard = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('order_name', 'idcard')
                ->count();
        $totalordernota = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('order_name', 'nota')
                ->count();
        $totalorderpackaging = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('order_name', 'packaging')
                ->count();
        $totalordertshirt = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->where('order_name', 'tshirt')
                ->count();

        $orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_status_date', 'DESC')
                ->paginate(10);
        return View::make('admin.report.produk', compact('totalorder', $totalorder, 'totalorderbrosur', $totalorderbrosur, 'totalorderidcard', $totalorderidcard, 'totalordernota', $totalordernota, 'totalorderpackaging', $totalorderpackaging, 'totalordertshirt', $totalordertshirt, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

    public function postProduk() {
        $b = Session::get('bulan');
        $t = Session::get('tahun');

        if (Input::get('filter') == 'type') {
            Session::put('list', Input::get('typeby'));
            if (Input::get('typeby') == 'all') {
                $orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                        ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_status_date', 'DESC')
                        ->paginate(10);
            } else {
                $orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                        ->where('order_name', Input::get('typeby'))
                        ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_status_date', 'DESC')
                        ->paginate(10);
            }
        } else if (Input::get('filter') == 'keyword') {
            if (Input::get('filterby') == 'code') {
                $orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                        ->where('order_code', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_status_date', 'DESC')
                        ->paginate(10);
            } else if (Input::get('filterby') == 'name') {
                $orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                        ->where('customer_name', 'LIKE', '%' . Input::get('keyword') . '%')
                        ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_status_date', 'DESC')
                        ->paginate(10);
            }
        } else {
            if (Session::get('list') == 'all') {
                $orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                        ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_status_date', 'DESC')
                        ->paginate(10);
            } else {
                $orderlistorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
                        ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                        ->where('order_name', Session::get('list'))
                        ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                        ->groupBy('order_id')
                        ->orderBy('order_status_date', 'DESC')
                        ->paginate(10);
            }
        }

        $totalorder = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderbrosur = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'brochure')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderidcard = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'idcard')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordernota = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'nota')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpackaging = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'packaging')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordertshirt = DB::table('sys_order')
       			->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'tshirt')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();



        return View::make('admin.report.produk', compact('totalorder', $totalorder, 'totalorderbrosur', $totalorderbrosur, 'totalorderidcard', $totalorderidcard, 'totalordernota', $totalordernota, 'totalorderpackaging', $totalorderpackaging, 'totalordertshirt', $totalordertshirt, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

    public function postProdukbulan() {
        Session::forget('list');
        if (Input::get('bulan') == "") {
            $b = Session::get('bulan');
        } else {
            $b = Input::get('bulan');
            Session::put('bulan', Input::get('bulan'));
        }
        if (Input::get('tahun') == "") {
            $t = Session::get('tahun');
        } else {
            $t = Input::get('tahun');
            Session::put('tahun', Input::get('tahun'));
        }


        $orderlistorder = DB::table('sys_order')
                ->join('sys_customer', 'order_customer_id', '=', 'customer_id')
                ->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->groupBy('order_id')
                ->orderBy('order_status_date', 'DESC')
                ->paginate(10);
        $totalorder = DB::table('sys_order')
      			  ->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderbrosur = DB::table('sys_order')
      			  ->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'brochure')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderidcard = DB::table('sys_order')
      			  ->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'idcard')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordernota = DB::table('sys_order')
       			 ->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'nota')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalorderpackaging = DB::table('sys_order')
       			 ->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'packaging')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();
        $totalordertshirt = DB::table('sys_order')
      			  ->join('sys_order_status','order_status_order_id','=','order_id')
				->where('order_status','=','Delivered')
				->where('order_status_type','=','Delivered')
                ->where('order_name', 'tshirt')
                ->where('order_name', 'tshirt')
                ->whereBetween('order_status_date', array($t . '-' . $b . '-01 00:00:00', $t . '-' . $b . '-31 23:59:59'))
                ->count();

        return View::make('admin.report.produk', compact('totalorder', $totalorder, 'totalorderbrosur', $totalorderbrosur, 'totalorderidcard', $totalorderidcard, 'totalordernota', $totalordernota, 'totalorderpackaging', $totalorderpackaging, 'totalordertshirt', $totalordertshirt, 't', $t, 'b', $b, 'orderlistorder', $orderlistorder));
    }

}